<?php
/**
 * This set of libraries allow to use object to persist values inside
 * a mysql database.
 * It allow to design object with own relationships and add many methods
 * to interact with html forms, object comparing, casting objects etc.
 * 
 * Complete documentation will be founds on the web site
 * 
 * Copyright (c) 2012 Principe Orazio
 * http://www.dbpersister.com/
 *
 * Licensed under LGPL
 * http://www.opensource.org/licenses/lgpl-3.0.html
 *
 * Launch  : June 2012
 * Last version: February 2013
 * 
 * Version : 2.0
 */



/*
 * This interface is used to mapping the database tables
 * with own relationships
 *
 * @author  Principe Orazio
 * @email   orazio.principe@gmail.com
 * @version 1.0
 * @date    2013-02-20
 */
interface IDbPersister_Map
{
    
    const KEY_TYPE_CHECK_BEFORE           = "check";
    const KEY_TYPE_EXECUTE_AFTER          = "exec";
    
    //Operations
    const OPERATION_CHECK_BEFORE_INSERT   = 100;
    const OPERATION_EXECUTE_AFTER_INSERT  = 110;
    const OPERATION_CHECK_BEFORE_UPDATE   = 200;
    const OPERATION_EXECUTE_AFTER_UPDATE  = 210;
    const OPERATION_CHECK_BEFORE_DELETE   = 300;
    const OPERATION_EXECUTE_AFTER_DELETE  = 310;
    
    //Conditions over operations
    const CONDITION_IGNORE = 1;
    const CONDITION_CASCADE = 2;
    const CONDITION_RESTRICT = 3;
    const CONDITION_SET_NULL = 4;
    

    /**
     * This method must return all persister objects
     * linked with one or more fields to this
     * 
     * @return DbMySql_Persister[] Related tables
     */
    function getDbMap();

    
    /**
     * Joined fields with that must do one or more
     * operation before insert
     * 
     * @param array $rel_fields
     */
    function fireOnBeforeInsertRelatedObject(DbMySql_Persister $obj, $map);

    /**
     * Joined fields with that must do one or more
     * operation before update
     * @param array $rel_fields
     */
    function fireOnBeforeUpdateRelatedObject(DbMySql_Persister $obj, $map);

    
    /**
     * Joined fields with that must do one or more
     * operation before delete
     * @param array $rel_fields
     */
    function fireOnBeforeDeleteRelatedObject(DbMySql_Persister $obj, $map);

    
    /**
     * Joined fields with that must do one or more
     * operation after insert
     * @param array $rel_fields
     */
    function fireOnAfterInsertRelatedObject(DbMySql_Persister $obj, $map);
    
    /**
     * Joined fields with that must do one or more
     * operation after update
     * @param array $rel_fields
     */
    function fireOnAfterUpdateRelatedObject(DbMySql_Persister $obj, $map);
    
    /**
     * Joined fields with that must do one or more
     * operation after delete
     * @param array $rel_fields
     */
    function fireOnAfterDeleteRelatedObject(DbMySql_Persister $obj, $map);
    
    
}


if(!class_exists("Castable")) {
/**
 * Castable
 * Casts an object to the class or interface represented 
 * by the second Class parameter. 
 * 
 * @author oprincipe
 */
class Castable
{

    /**
     * Casts the object to the $obj class 
     * 
     * @param Object $obj
     */
    public function cast($obj)
    {
        $new_vars = get_class_vars(get_class($obj));
        foreach($new_vars as $var => $el)
        {
            $obj->$var = $this->$var;
        }

        return $obj;
    }

    /**
     * Cast the first object to the second class
     * using serialization
     * 
     * @param Object $obj
     * @param Object $to_class
     * @return boolean
     */
    public static function force_cast($obj, $to_class) {
        if(class_exists($to_class)) {
            $obj_in = serialize($obj);
            $obj_out = 'O:' . strlen($to_class) . ':"' . $to_class . '":' . substr($obj_in, $obj_in[2] + 7);
            return unserialize($obj_out);
        }
        else {
            return false;
        }
    }

}

}


/**
 * Use this class to initialize object values
 * derived by POSTS or others channels
 * 
 * Fields starting with userscore will be used as private fields
 * 
 * Copyright (c) 2012 Principe Orazio
 * http://www.dbpersister.com/
 *
 * Licensed under LGPL
 * http://www.opensource.org/licenses/lgpl-3.0.html
 *
 * Launch  : June 2012
 * Version : 1.0
 */
class DbMySql_Initializer extends Castable
{

    //Fields list
    private $_fields;
    
    
    function __construct() {
        $this->setFields();
    }

    /**
     * Retreive object declared fields
     * 
     * @return boolean
     */
    private function setFields()
    {
        $els = get_class_vars(get_class($this));
        $this->_fields = $els;
        return true;
    }
    
    /**
     * Take off a field from the object
     * 
     * @param string $fieldName
     */
    public function unsetField($fieldName)
    {
        unset($this->_fields[$fieldName]);
    }
    
    /**
     * Return the list of object's fields
     * @return array
     */
    public function getFields()
    {
        if(empty($this->_fields)) $this->setFields();
        return $this->_fields;
    }



    /**
     * Initialize the object with resultset data or a data row
     * The resultsets supported are:
     *  - mysqli_result (object reference)
     *  - CI_DB_mysql_result (codeigniter object resultset)
     *  - array data containing fields name as key and field values as value
     * 
     * @param mixed $rs
     * @return boolean
     * 
     * @version 1.1
     */
    public function initialize($rs)
    {

        if(empty($rs)) {
            return false;
        }

        if($rs instanceof mysqli_result) {
            if($rs->num_rows <= 0) {
                return false;
            }

            //$data = $rs->fetch_assoc();
            $obj = $rs->fetch_object(get_called_class());
            $obj->cast($this);
            unset($obj);
            
            return true;
        } else if($rs instanceof CI_DB_mysql_result) {
            $data = $rs->_fetch_assoc();
        } else {
            //$rs is an array already done
            $data = $rs;
        }

        
        foreach($this->_fields as $field => $value)
        {
            if(strpos($field, "_") === 0) {
                continue;
            }

            try
            {
                if(is_array($data)) {
                    //get fetch_array or assoc results
                    $this->$field = @$data[$field];
                } else {
                    $this->$field = @$data->$field;
                }
            }
            catch(Exception $exception)
            {
                
            }
        }


        return true;
    }

    /**
     * Set class variables from post or get of a submitted form
     * All fields starting with underscore are jumped cause interpreded as privates
     * fields
     * 
     * @param string $prefix
     * @param string $suffix
     */
    public function setFromSubmit($prefix = "", $suffix = "", $method = "POST", $onlySetFields = false)
    {
        foreach($this->_fields as $field => $value)
        {
            if(strpos($field, "_") === 0) {
                continue;
            }
            if($method == "POST") {

                if($onlySetFields) {
                    if(isset($_POST[$prefix . $field . $suffix])) {
                        $this->$field = @$_POST[$prefix . $field . $suffix] . "";
                    }
                } else {
                    $this->$field = @$_POST[$prefix . $field . $suffix] . "";
                }
            } else {
                if($onlySetFields) {
                    if(isset($_GET[$prefix . $field . $suffix])) {
                        $this->$field = @$_GET[$prefix . $field . $suffix] . "";
                    }
                } else {
                    $this->$field = @$_GET[$prefix . $field . $suffix] . "";
                }
            }
        }
    }

    
    
    /**
     * Fill the variable $data with an array containing
     * all object fields values with fields name as key and field values as value
     * 
     * @param array $data variable passed as referenced variable
     * @return boolean
     */
    public function setArrayVarsToData(&$data)
    {
        foreach($this->_fields as $field => $value)
        {
            if(is_string($this->$field)) {
                if(mb_detect_encoding($this->$field) != "UTF-8") {
                    $data[$field] = @utf8_decode($this->$field);
                } else {
                    $data[$field] = $this->$field;
                }
            } else {
                $data[$field] = $this->$field;
            }
        }

        return true;
    }

    
    /**
     * Fill the object with the variables contained into $data assuming
     * fields name as key and field values as value
     * 
     * @param array $data variable passed as referenced variable
     * @return boolean
     */
    public function setDataFromArrayVars($data)
    {
        if(!is_array($data))
            return false;

        foreach($this->_fields as $field => $value)
        {
            if(!array_key_exists($field, $data)) {
                continue;
            }

            if(is_string($data[$field])) {
                if(mb_detect_encoding($data[$field]) != "UTF-8") {
                    $this->$field = @utf8_decode($data[$field]);
                } else {
                    $this->$field = $data[$field];
                }
            } else {
                $this->$field = $data[$field];
            }
        }

        return true;
    }
    
    
    
    
    /**
     * Clear the objects fields setting them with a blank string
     * excluding privates fields
     * 
     * @return boolean
     */
    public function clear()
    {
        foreach($this->_fields as $field => $value)
        {
            if(strpos($field, "_") === 0) {
                continue;
            }

            $this->$field = "";
        }
        return true;
    }

}


/**
 * Db persister extends all methods to
 * <br>load, save and delete an object from db
 * <br>Manage relationships with objects map
 * 
 * Copyright (c) 2012 Principe Orazio
 * http://www.dbpersister.com/
 *
 * Licensed under LGPL
 * http://www.opensource.org/licenses/lgpl-3.0.html
 *
 */
abstract class DbMySql_Persister extends DbMySql_Initializer implements IDbPersister_Map
{

    /**
     * @var DbMySqli_Settings 
     */
    private $dbClass;
    
    private $skipKeysVerifier;
    
    private $dbDebugger      = false;
    private $dbSqlLikes      = false;
    private $dbSqlWheres     = false;
    private $dbSqlLimitFrom  = false;
    private $dbSqlLimitTo    = false;
    

    /**
     * Used to store original key values 
     * in case of updating primary key value
     * 
     * @var mixed
     */
    private $origKeyValues   = null;
    
    /**
     * Used to store the original values of all fields
     * used to rollback operations and/or updating references fields
     * 
     * @var string
     */
    private $memoryObject    = null;
    
    /**
     * This static method is required cause every object that extends this
     * <br>will be implemts his table name and eventually his database name
     */
    public abstract function getTableName();

    /**
     * This method to set the primary key of the table
     * <br>it can return a string or an array
     *
     * @return mixed
     */
    public abstract function getPrimaryKey();

    
    /**
     * return the database used for data. This method can be ovverrides
     * <br>if the object want to use a different db name to that used
     * <br>by the DbMySqli_Settings one
     *
     * @version 1.2 fix bug to unset database name for calling outside class range
     * 
     * @return string
     */
    public function getDatabaseName()
    {
        if(empty($this->dbClass->database)) {
            $this->getDbClass();
        }
        return $this->dbClass->database;
    }


    
    function __construct($rs = "") {
        parent::__construct();
        $this->initialize($rs);
        $this->skipKeysVerifier = false;
    }

    
    /**
     * Return the original key values loaded after initializinig
     * @return midex
     */
    public function getOrigKeyValues() {
        return $this->origKeyValues;
    }

        
    /**
     * With this method it's possible to modify/alter
     * keys and values for this object
     * 
     * @param array $mixed Mixed custom values
     */
    public function setOrigKeyValues($mixed) {
        $this->origKeyValues = $mixed;
    }
    
    
    /**
     * Store original key and fields values after initialized object
     * 
     * This method is used by the initialize() method automatically
     * but it's possible to use it if is necessary to change one or more
     * of the primaries object keys
     * 
     * @return boolean
     */
    public function storeOriginalValues()
    {
        //Test of dbMap, if empty there is not reason to store old values
        $dbmap = $this->getDbMap();
        if(empty($dbmap)) {
            return true;
        }
        
        $keys = $this->getPrimaryKey();

        if(is_array($keys)) {
            foreach($keys as $key=>$value) {
                if(empty($this->$value) && $this->skipKeysVerifier === false) return false;

                $this->origKeyValues[$value] = $this->$value;

            }
        }
        else {
            //Single key value
            if(empty($this->$keys)) return false;

            $this->origKeyValues[$keys] = $this->$keys;
        }
        
        
        //Store serialized memory object
        $this->memoryObject = clone $this;


        return true;

    }
    
    
    /**
     * Get the field value from the memoryObject
     * stored before any changes
     * 
     * @param string $fieldName
     * @return midex
     */
    function getOrigValue($fieldName)
    {
        return $this->memoryObject->$fieldName;
    }
    
    
    /**
     * Overriding with storing original object values
     * 
     * @see 
     * @param midex $rs
     * @return boolean
     */
    public function initialize($rs) {
        if(!parent::initialize($rs)) {
            return false;
        }
        
        $this->storeOriginalValues();
        return true;
    }

    
    /**
     * Return the class used by the persister to iterate with
     * the database
     * 
     * @return mixed
     */
    public function getDbClass() {
        if(empty($this->dbClass)) {
            $this->setDb();
        }
        
        return $this->dbClass;
    }


    /**
     * Execute the query to database to skip key verify for innodb relationships
     * And if presents for the mapped object
     * 
     * Execute in order all following queries:
     *  - SET unique_checks = 0
     *  - SET foreign_key_checks = 0
     * 
     * @version 1.2
     */
    public function disableKeyVerifier() {
        $this->getDbClass();
        $this->dbClass->query("SET unique_checks = 0");        
        $this->dbClass->query("SET foreign_key_checks = 0");
        
        $this->skipKeysVerifier = true;
    }

    /**
     * Execute the query to database to enable key verify for innodb relationships
     * Execute in order all following queries:
     *  - SET unique_checks = 1
     *  - SET foreign_key_checks = 1
     * 
     * @version 1.2
     */
    public function enableKeyVerifier() {
        $this->getDbClass();
        $this->dbClass->query("SET unique_checks = 1");        
        $this->dbClass->query("SET foreign_key_checks = 1");
        
        $this->skipKeysVerifier = false;
    }

    /**
     * Set if show or not errors from the database
     */
    public function suppressErrors() {
        $this->setDb();
        $this->dbDebugger = false;
    }

    /**
     * Set if show or not errors from the database
     */
    public function enableErrors() {
        $this->dbDebugger = true;
    }

    
    /**
     * Destruct the class and the database reference
     */
    public function __destruct() {
        unset($this->dbClass);
    }

    
    /**
     * Return the empty instance of the class
     * 
     * <br>if $primaryKeys is passed it will return
     * <br>the object filled by calling the method
     * <br>findByPrimaryKey
     *
     * @param mixed $primaryKeys
     *
     * @see findByPrimaryKey
     * @return DbMySql_Persister
     */
    public static function getInstance($primaryKeys = null) {
        $classname = get_called_class();
        $obj = new $classname();
        if(!empty($primaryKeys)) {
            $obj->findByPrimaryKey($primaryKeys);
        }

        return $obj;
    }


    /**
     * This method allow to parse all variables passed by html forms
     * in post building the sql where claused that will be used
     * on search events
     *
     * @param string $prefix
     * @param string $suffix
     * @return boolean
     */
    public function processPostSearchVariabiles($prefix = "", $suffix = "", $table_prefix = "")
    {
        $this->getDbClass();
        
        $classname = get_called_class();
        if(empty($table_prefix)) {
            $table_prefix = $this->getTableName();
        }

        $vars = get_class_vars($classname);
        foreach($vars as $var=>$value):
            if(!empty($_POST[$prefix.$var.$suffix])) {
                $value = utf8_decode($_POST[$prefix.$var.$suffix]);
                $this->dbSqlLikes[] = " `".$table_prefix."`.`".$var."` LIKE '".addslashes($value)."' ";
            }
        endforeach;

        return true;
        
    }


    /**
     * Initialize the DbMySqli_Settings object, if is not passed
     * the class check if is already set before
     * 
     * @param mysqli $mysqli Mysqli object or DbMySqli_Settings
     * @return boolean
     */
    public function setDb($mysqli = null)
    {
        if($mysqli instanceof DbMySqli_Settings) {
            $this->dbClass = $mysqli;
        }
        else if($mysqli instanceof mysqli) {
            $this->dbClass = $mysqli;  
            $rs = $mysqli->query("SELECT DATABASE()");
            $row = $rs->fetch_row();
            $this->dbClass->database = $row[0];
        }
        else if(empty($mysqli)) {
            if(!($this->dbClass instanceof mysqli)) {
                //Check the default configuration and connect
                $this->dbClass = new DbMySqli_Settings();
                $this->dbClass->connect();
            }
        }
        else {
            throw new Exception("Connection not supported, you can use mysqli driver or setup the DbMySqli_Settings class fields");
        }
        
        return true;
    }



    /**
     * Execute a custom query to the database 
     * using current connection and return result
     *
     * @param string $sql
     * @return mysqli_result
     */
    public function getCustomQueryResult($sql)
    {
        $this->setDb();
        return $this->dbClass->query($sql);
    }



    /**
     * If the primary key is an array, $values must
     * <br>be an array too, otherwise it must be a string
     *
     * @param string/array $values
     * @param boolean $returnEmptyObject allow to return only the instance of object
     */
    public function findByPrimaryKey($values, $returnEmptyObject = true)
    {
        if(empty($values)) {
            if($returnEmptyObject) {
                return $this;
            }
            else {
                throw new Exception(__METHOD__." Search value not set");
            }
        }

        $this->setDb();
        $keys = $this->getPrimaryKey();

        if(is_array($keys)) {

            $keys_count = count($keys);

            //The parameter should be an array
            if(!is_array($values)) throw new Exception(__METHOD__." Key values must be an array of $keys_count elements");

            //Search keys must be equals to parameters count
            if($keys_count != count($values)) throw new Exception(__METHOD__." Key values count doesn't match");

            //Reset previous values
            unset($this->dbSqlWheres);
            for($i = 0; $i < $keys_count; $i++):
                $this->dbSqlWheres[$keys[$i]] = $values[$i];
            endfor;

        }
        else {
            //Parameter should not be an array
            if(is_array($values)) throw new Exception("Key values must be a single value");
            $this->dbSqlWheres[$keys] = $values;
        }


        //Initialize the object calling get method
        $this->initialize($this->dbClass->query($this->getSelectSql()));
        
    }



    /**
     * This method find only the first occurrence
     * and make the search using keys and values combination
     *
     * @param string|array $values
     * @param boolean $returnEmptyObject allow to return only the instance of object
     * 
     * @return boolean|Exception
     */
    public function findByUniqueKey($keys, $values, $returnEmptyObject = true)
    {
        if(empty($values)) {
            if($returnEmptyObject) {
                return $this;
            }
            else {
                throw new Exception("Search value not set");
            }
        }

        $this->setDb();

        if(is_array($keys)) {

            $keys_count = count($keys);

            //The parameter should be an array
            if(!is_array($values)) throw new Exception("Key values must be an array of $keys_count elements");

            //Search keys must be equals to parameters count
            if($keys_count != count($values)) throw new Exception("Key values count doesn't match");

            for($i = 0; $i < $keys_count; $i++):
                $this->dbSqlWheres[$keys[$i]] = $values[$i];
            endfor;

        }
        else {
            //Parameter should not be an array
            if(is_array($values)) throw new Exception("Key values must be a single value");
            $this->dbSqlWheres[$keys] = $values;
        }

        //Limit the result to 1 for speedly the search
        $this->dbSqlLimitFrom = 0;
        $this->dbSqlLimitTo = 1;

        //Initialize the object
        $this->initialize($this->dbClass->query($this->getSelectSql()));
        
        return true;
    }


    
    /**
     * If $fields is an array $values must
     * <br>be an array, otherwise it must be a string
     * <br>
     * <br>This method find all the occurrence and return the list of object founds
     *
     * @param string|array $fields
     * @param string|array $values
     * @param int $limitFrom start from a resultset pointer
     * @param int $records number of results to retreive (0 = full list)
     * @param boolean $getResultSet return the resultset pointer insthead the object array
     * 
     * @return mysqli_result|array Result of the query
     * 
     * @version 1.0
     * @date    2012-02-22
     */
    public function find($fields, $values, $limitFrom, $records, $getResultSet = false)
    {
        $this->setDb();

        unset($this->dbSqlWheres);
        
        if(is_array($fields)) {

            $keys_count = count($fields);

            //The parameter should be an array
            if(!is_array($values)) throw new Exception("Key values must be an array of $keys_count elements");

            //Search keys must be equals to parameters count
            if($keys_count != count($values)) throw new Exception("Key values count doesn't match");

            foreach($fields as $relField=>$settings):
                foreach($settings as $thisField=>$thisValue):
                    //Skip for null values
                    $this->dbSqlWheres[$relField] = $thisValue;
                endforeach;            
            endforeach;
            
        }
        else {
            //Parameter should not be an array
            if(is_array($values)) throw new Exception("Key values must be a single value");
            $this->dbSqlWheres[$fields] = $values;
        }

        //Limit the result to 1 for speedly the search
        $this->dbSqlLimitFrom = (int) $limitFrom;
        $this->dbSqlLimitTo = (int) $records;

        //Initialize the object
        $rs = $this->dbClass->query($this->getSelectSql());
        if($getResultSet) {
            return $rs;
        }
        
        $results = array();
        while($row = $rs->fetch_object(get_class($this))) 
        {
            $results[] = $row;
        }
        
        return $results;
    }


    
    
    
    

    /**
     * This method check if all keys all filled and if true
     * <br>execute the update statement else execute the insert
     * <br>method
     * <br>
     * <br>Note: this method can be call only if the
     * <br>primary key is an autoincrement field
     */
    public function save()
    {
        $this->setDb();

        if($this->keysFilled()) {
            return $this->update();
        }
        else {
            return $this->insert();
        }
    }


    /**
     * Check if the object keys are full or not
     * 
     * @version 1.1
     */
    private function keysFilled()
    {
        if($this->skipKeysVerifier) {
            return true;
        }

        $els = $this->getPrimaryKey();
        if(is_array($els)) {
            foreach($els as $el)
            {
                if(empty($this->$el)) return false;
            }
            return true;
        }
        else {
            return !empty($this->$els);
        }
    }


    /**
     * if the object has a single primary key
     * <br>when the object is inserted his primary key will
     * <br>be set by all the values produced by the db
     */
    public function insert()
    {
        $this->setDb();
        
        //Fire events
        $this-> _fireEvents(IDbPersister_Map::OPERATION_CHECK_BEFORE_INSERT);
        
        $res = $this->dbClass->query($this->getInsertSql());
        if(!$res && !$this->dbDebugger) {
            throw new Exception($this->dbClass->error);
        }
        
        //Fire events
        $this-> _fireEvents(IDbPersister_Map::OPERATION_EXECUTE_AFTER_INSERT);
        
        if(!is_array($this->getPrimaryKey())) {
            $key = $this->getPrimaryKey();
            $inserted_id = $this->dbClass->insert_id;
            if(!empty($inserted_id)) {
                $this->$key = $inserted_id;
            }
        }
        
        return $res;
    }


    private function _getSelectAndOr($string, $or_statement = false)
    {
        return (strpos(strtolower($string), " where ") !== FALSE) ? ($or_statement) ? "OR" : "AND" : " WHERE ";
    }
    
    
    
    /*
     * @todo:
     *      - or clause
     */
    private function getSelectSql($table = "")
    {
        $sql = "SELECT * FROM ";
        $_table = (empty($table)) ? $this->getTableName() : $table;
        $sql .= "`".$this->getDatabaseName()."`.`".$_table."` ";
        
        //Build where
        if(!empty($this->dbSqlWheres)) {
            foreach($this->dbSqlWheres as $field=>$value)
            {
                $sql .= $this->_getSelectAndOr($sql, false) . " `".$field."`" . " = '".addslashes($value)."' ";
            }
        }
        
        if(!empty($this->dbSqlLikes)) {
            foreach($this->dbSqlLikes as $field=>$value)
            {
                $sql .= $this->_getSelectAndOr($sql, false) . " `".$field."`" . " LIKE '%".addslashes($value)."%' ";
            }
        }
        
        
        if($this->dbSqlLimitTo > 0) {
            $sql .= " LIMIT ";
            if($this->dbSqlLimitFrom !== FALSE) {
                $sql .= $this->dbSqlLimitFrom.",";
            }
            $sql .= $this->dbSqlLimitTo;
        }
        
        
        return $sql;        
    }
    
    
    /**
     * return the sql data for inserting
     *
     * @return string
     */
    public function getInsertSql($onlyFields = false, $onlyValues = false, $relace = false, $ignore_clause = false)
    {
        $this->setDb();

        $sqlInit = "INSERT INTO ";
        $_table = $this->getTableName();
        $sqlInit .= "`".$this->getDatabaseName()."`.`".$_table."` ";
        
        
        if($relace) {
            $sqlInit = str_replace("INSERT INTO ", "REPLACE INTO ", $sqlInit);
        }

        if($ignore_clause) {
            if(!$relace) {
                $sqlInit = str_replace("INSERT INTO ", "INSERT IGNORE INTO ", $sqlInit);
            }
            else {
                //For replace ignore clause is not needed
            }
        }
        
        
        $sqlArrFields = array();
        $sqlArrValues = array();
        
        foreach($this->getFields() as $field=>$not_used)
        {
            if(strpos($field, "_") === 0) {
                //Private fields not processed
                continue;
            }


            if(is_string($this->$field)) {
                if(mb_detect_encoding($this->$field) != "UTF-8") {
                    $value = @utf8_decode($this->$field);
                } else {
                    $value = $this->$field;
                }
            } else {
                $value = $this->$field;
            }

            $sqlArrFields[] = "`".$field."`";
            $sqlArrValues[] = ($value === NULL) ? "{NULL}" : "'".addslashes($value)."'";

        }
        
        
        //Build sql field variable
        $sqlFields = implode(",",$sqlArrFields);
        
        //Build sql field variable
        $sqlValues = str_replace('{NULL}','NULL',implode(",",$sqlArrValues));

        //Join to the original sql
        $sql = $sqlInit . " (".$sqlFields.") VALUES (".$sqlValues.") ";
                
        
        

        
        
        
        if($onlyFields == true) {
            $sql = $sqlInit . "(".$sqlFields.")";
        }

        if($onlyValues == true) {
            $sql = "(".$sqlValues.")";
        }

        return $sql;
    }


    /**
     * return the sql data for updating
     *
     * @return string
     */
    public function getUpdateSql($onlyFields = false, $onlyValues = false)
    {
        $this->setDb();
        
        
        $sqlInit = "UPDATE ";
        $_table = $this->getTableName();
        $sqlInit .= "`".$this->getDatabaseName()."`.`".$_table."` ";
        $sql = $sqlInit . " SET ";
        
        
        //Process fields and values
        $arr_fields_values = array();
        foreach($this->getFields() as $field=>$not_used)
        {
            if(strpos($field, "_") === 0) {
                //Private fields not processed
                continue;
            }
            
            
            if(is_string($this->$field)) {
                if(mb_detect_encoding($this->$field) != "UTF-8") {
                    $value = @utf8_decode($this->$field);
                } else {
                    $value = $this->$field;
                }
            } else {
                $value = $this->$field;
            }
            
            $sqlValue = ($value === null) ? " NULL " : " '".addslashes($value)."' ";
            $arr_fields_values[] = "`".$field."` =  $sqlValue";
        }
        $sqlFields = implode(",",$arr_fields_values);
        $sql .= $sqlFields;
        
       
        
        $this->setDbWhereClause();
        if(!empty($this->dbSqlWheres)) {
            foreach($this->dbSqlWheres as $field=>$value)
            {
                $sql .= $this->_getSelectAndOr($sql, false) . " `".$field."`" . " = '".addslashes($value)."' ";
            }
        }
        
        
        if($onlyFields == true) {
            $els = explode(" SET ", $sql);
            $sql = @trim($els[0]);
        }

        if($onlyValues == true) {
            $els = explode(" SET ", $sql);
            $sql = @trim($els[1]);
        }

        return $sql;
    }

    /**
     * return the sql data for deleting
     *
     * @return string
     */
    public function getDeleteSql()
    {
        $this->setDb();
        
        
        $sql = "DELETE FROM ";
        $_table = $this->getTableName();
        $sql .= "`".$this->getDatabaseName()."`.`".$_table."` ";
         
        //Set where clause
        $this->setDbWhereClause();
        if(!empty($this->dbSqlWheres)) {
            foreach($this->dbSqlWheres as $field=>$value)
            {
                $sql .= $this->_getSelectAndOr($sql, false) . " `".$field."`" . " = '".addslashes($value)."' ";
            }
        }
        
        

        return $sql;
    }



    /**
     * Executing a bulk inserts with $concurrent_records records
     * 
     * @param int $concurrent_records (max 1000)
     * @param array $sql_arr_values
     */
    public function bulkInsert(&$sql_arr_values, $concurrent_records = 1000, $print_log = false,
            $relace = false, $ignore_clause = false)
    {
        $this->setDb();

        $sql_init = $this->getInsertSql(true, false, $relace, $ignore_clause);

        $msg = "";
        if($print_log) {
            $msg = "Executing operations...";
            syslog(LOG_DEBUG, $msg);
        }
        
        if(empty($concurrent_records) || (int) $concurrent_records > 1000) {
            $concurrent_records = 1000;
        }
        if($print_log) {
            $msg = "Executing operations...";
            syslog(LOG_DEBUG, $msg."\n\tExecuting $concurrent_records operations in the same time");
        }

        $counter = 0;
        $sql_arr_queued = array();

        foreach($sql_arr_values as $sql):

            if(empty($sql)) continue;

            $counter++;

            $sql_arr_queued[] = $sql;

            //Execute X concurrent queries
            if($counter % $concurrent_records == 0) {
                $sql_values = implode(",\n",$sql_arr_queued);
                $sql = $sql_init." \n VALUES \n ".$sql_values;
                if(!$this->getCustomQueryResult($sql)) {
                    file_put_contents("sql_errors.list", $sql);
                    throw new Exception($sql."\n\nErrore: ".$this->getDbClass()->error."\n\n");
                }

                //Empty queued queries
                $sql_arr_queued = array();
            }

        endforeach;

        //Execute remaining queries
        if(!empty($sql_arr_queued)) {
            $sql_values = implode(",\n",$sql_arr_queued);
            $sql = $sql_init." \n VALUES \n ".$sql_values;
            if(!$this->getCustomQueryResult($sql)) {
                file_put_contents("sql_errors.list", $sql);
                throw new Exception($sql);
            }
            if($print_log) {
                $msg = "Executing latest operations...";
                syslog(LOG_DEBUG, $msg."\n\t - executed: $counter operations (100%)");
            }
        }

        return true;
    }






    /**
     * Set the where clause for the db.
     * <br>If one of the primary keys is not set it will return false
     *
     *
     * @version 1.1 Change where clause with the original key(s) values
     * @return boolean
     */
    private function setDbWhereClause()
    {

        $this->setDb();
        
        $keys = $this->getPrimaryKey();
        $origKeys = $this->getOrigKeyValues();
        
        $this->dbSqlWheres = array(); // -- test orazio 2013-07-02
        if($origKeys !== null) {
            $keys = $origKeys;
            $this->dbSqlWheres = array();
        }        
        
        if(is_array($keys)) {
            foreach($keys as $key=>$value) {
                
                $arr_key    = ($this->getOrigKeyValues() !== NULL) ? $key : $value;
                $arr_value  = ($this->getOrigKeyValues() !== NULL) ? $this->origKeyValues[$key] : $this->$value;
                if(is_array($arr_value)) {
                    $arr_value = $arr_value[key($arr_value)];
                }
                
                if(empty($arr_value) && $this->skipKeysVerifier === false) return false;
                $this->dbSqlWheres[$arr_key] = $arr_value;
                
            }
            return true;
        }
        else {
            //Single key value
            if(empty($this->$keys)) return false;
            $this->dbSqlWheres[$keys] = $this->$keys;
            
            return true;
        }
    }


    /**
     * @return int
     * @throws Exception
     */
    public function update()
    {
        $this->setDb();
        if(!$this->setDbWhereClause()) throw new Exception(__METHOD__.": Primary keys not filled");
        
        //Fire events
        $this-> _fireEvents(IDbPersister_Map::OPERATION_CHECK_BEFORE_UPDATE);
        
        $res = $this->dbClass->real_query($this->getUpdateSql());
        if(!$res && !$this->dbDebugger) {
            $error = $this->dbClass->error;
            throw new Exception($error);
        }
        
        //Fire events
        $this-> _fireEvents(IDbPersister_Map::OPERATION_EXECUTE_AFTER_UPDATE);
        
        
        return $res;
    }


    /**
     * delete the object from the database
     * @todo Build the query
     * 
     * @return int
     * @throws Exception
     */
    public function delete()
    {
        $this->setDb();
        
        //Fire events
        $this-> _fireEvents(IDbPersister_Map::OPERATION_CHECK_BEFORE_DELETE);
        
        if(!$this->setDbWhereClause()) throw new Exception(__METHOD__.": Primary keys not filled");
        $res = $this->dbClass->query($this->getDeleteSql());
        
        //Fire events
        $this-> _fireEvents(IDbPersister_Map::OPERATION_EXECUTE_AFTER_DELETE);
        
        return $res;
    }


    /**
     * delete the object from the database
     * and insert a new one
     */
    public function replace()
    {
        try { $this->delete(); }
        catch(Exception $e) {}
        return $this->insert();
    }




    /**
     * Compare all keys and values between
     * <br>
     * the current object and the object passed by parameter
     *
     * @param DbMySql_Persister $obj
     *
     * @return boolean
     */
    public function equals($obj)
    {
        if(!($obj instanceof DbMySql_Persister)) {
            return false;
        }

        $key = $this->getPrimaryKey();
        $obj_key = $obj->getPrimaryKey();

        if($key != $obj_key) {
            return false;
        }

        if(is_array($key)) {
            $els = count($key);

            //Check all key values
            for($i = 0; $i < $els; $i++)
            {
                if($this->$key[$i] != $obj->$obj_key[$i]) {
                    return false;
                }
            }
        }
        else {
            $orig_key = $this->$key;
            $objt_key = $obj->$obj_key;
            if($orig_key != $objt_key) {
                return false;
            }
        }


        return true;
    }


    
    /**
     * Fire the event specified by the object mapping
     * if the keys verifier is enabled
     * 
     * @param int $operation event name from IDbPersister_Map OPERATIONS constants
     * @return boolean
     */
    private function _fireEvents($operation)
    {
        if($this->skipKeysVerifier) { 
            return true;
        }
    
        if($this instanceof IDbPersister_Map) {
            //Take operations befor insert for linked objects
            $mapped_objects = $this->getDbMap();
            if(empty($mapped_objects)) {
                return true;
            }
            
            foreach($mapped_objects as $class=>$map):
                
                $event_to_fire = @$map[$operation];
                if(!empty($event_to_fire)) {
                    
                    $obj = new $class;
                    
                    if($operation == IDbPersister_Map::OPERATION_CHECK_BEFORE_INSERT) {
                        $this->fireOnBeforeInsertRelatedObject($obj, $map);
                    }
                    else if($operation == IDbPersister_Map::OPERATION_EXECUTE_AFTER_INSERT) {                        
                        $this->fireOnAfterInsertRelatedObject($obj, $map);
                    }
                    else if($operation == IDbPersister_Map::OPERATION_CHECK_BEFORE_UPDATE) {
                        $this->fireOnBeforeUpdateRelatedObject($obj, $map);
                    }
                    else if($operation == IDbPersister_Map::OPERATION_EXECUTE_AFTER_UPDATE) {                        
                        $this->fireOnAfterUpdateRelatedObject($obj, $map);
                    }
                    else if($operation == IDbPersister_Map::OPERATION_CHECK_BEFORE_DELETE) {                        
                        $this->fireOnBeforeDeleteRelatedObject($obj, $map);
                    }
                    else if($operation == IDbPersister_Map::OPERATION_EXECUTE_AFTER_DELETE) {                        
                        $this->fireOnAfterDeleteRelatedObject($obj, $map);
                    }
                    
                    
                }                
            endforeach;
            
        }
        
        return true;
    }
    
    
    
    
    
    /**
     * This method must return all persister objects
     * linked with one or more fields to this
     * 
     * @return DbMySql_Persister[] Related tables
     */
    function getDbMap(){}

    
    /**
     * Search related object used by relations operations
     * 
     * @param DbMySql_Persister $obj    the object used for inside search (fill during operation)
     * @param array $map                mapped relationships
     * @param int   $operation          operation mapped by the interface
     * @param array $used_keys          key used to search related object (fill during operation)
     * @param array $used_values        values used to search related object (fill during operation)
     * @return boolean
     */
    private function _searchRelatedObject(DbMySql_Persister &$obj, $map, $operation, &$used_keys = null, &$used_values = null)
    {
        $key_condition = ($operation === IDbPersister_Map::OPERATION_CHECK_BEFORE_INSERT ||
                          $operation === IDbPersister_Map::OPERATION_CHECK_BEFORE_UPDATE ||
                          $operation === IDbPersister_Map::OPERATION_CHECK_BEFORE_DELETE) 
                ? IDbPersister_Map::KEY_TYPE_CHECK_BEFORE : IDbPersister_Map::KEY_TYPE_EXECUTE_AFTER;
                
        $used_keys   = $map[$key_condition];
        $search_keys = array_keys($used_keys);
        $used_values = array();
        foreach($used_keys as $relField=>$orig_settings):
            foreach($orig_settings as $thisField=>$thisValue):
                //Skip for null values
                if($thisValue === NULL) continue;
                $used_values[] = $thisValue;
            endforeach;            
        endforeach;

        //Nothing to do if there's not search values, possible made possible by SET_NULL condition
        if(empty($used_values)) {
            return true;
        }
        
        $obj->findByUniqueKey($search_keys, $used_values);
        
        return true;
    }
    
    
    /**
     * Joined fields with that must do one or more
     * operation before insert
     * 
     * Conditions supported:
     *  CONDITION_IGNORE    -> ignore if the related object doesn't exists
     *  CONDITION_RESTRICT  -> throw exception if the related object doesn't exists
     * 
     * @param array $rel_fields
     */
    function fireOnBeforeInsertRelatedObject(DbMySql_Persister $obj, $map)
    {
        $condition = @$map[IDbPersister_Map::OPERATION_CHECK_BEFORE_INSERT];
        if($condition === IDbPersister_Map::CONDITION_IGNORE) {
            //No problems here
            return true;
        }
        
        //Search related object
        $this->_searchRelatedObject($obj, $map, IDbPersister_Map::OPERATION_CHECK_BEFORE_INSERT, $used_keys, $used_values);

        //Nothing to do if there's not search values, possible made possible by SET_NULL condition
        if(empty($used_values)) {
            return true;
        }
        
        if(!$obj->keysFilled()) {
            
            //Object not founds
            
            //Verify condition to do
            if($condition === IDbPersister_Map::CONDITION_RESTRICT) {
                //Key must be present
                throw new Exception("RELATIONSHIP ERROR: 
                                    Method: ".__METHOD__."
                                    Object: ".get_class($obj)." 
                                    Keys: ".var_export($used_keys, true)."
                                    Dump: ".var_export($this, true));
            }
            
        }
        else {
            //Object founds
            return true;
        }
    }

    /**
     * Joined fields with that must do one or more
     * operation before update
     * 
     * Conditions supported:
     *  CONDITION_IGNORE    -> ignore if the related object doesn't exists
     *  CONDITION_RESTRICT  -> throw exception if the related object doesn't exists
     * 
     * @param array $rel_fields
     */
    function fireOnBeforeUpdateRelatedObject(DbMySql_Persister $obj, $map)
    {
        $condition = @$map[IDbPersister_Map::OPERATION_CHECK_BEFORE_UPDATE];
        if($condition === IDbPersister_Map::CONDITION_IGNORE) {
            //No problems here
            return true;
        }
        
        //Search related object
        $this->_searchRelatedObject($obj, $map, IDbPersister_Map::OPERATION_CHECK_BEFORE_UPDATE, $used_keys, $used_values);

        //Nothing to do if there's not search values, possible made possible by SET_NULL condition
        if(empty($used_values)) {
            return true;
        }
        
        if(!$obj->keysFilled()) {
            
            //Object not founds
            
            //Verify condition to do
            if($condition === IDbPersister_Map::CONDITION_RESTRICT) {
                //Key must be present
                throw new Exception("RELATIONSHIP ERROR: 
                                    Method: ".__METHOD__."
                                    Object: ".get_class($obj)." 
                                    Keys: ".var_export($used_keys, true)."
                                    Dump: ".var_export($this, true));
            }
            
        }
        else {
            //Object founds
            return true;
        }
        
    }

    
    /**
     * This operation can be fired before deleting father object
     * 
     * Conditions supported:
     *  CONDITION_IGNORE    -> ignore if the related object doesn't exists
     *  CONDITION_RESTRICT  -> throw exception if the related object exists
     * 
     * Joined fields with that must do one or more
     * operation before delete
     * @param array $rel_fields
     */
    function fireOnBeforeDeleteRelatedObject(DbMySql_Persister $obj, $map)
    {
        $condition = @$map[IDbPersister_Map::OPERATION_CHECK_BEFORE_DELETE];
        if($condition === IDbPersister_Map::CONDITION_IGNORE) {
            //No problems here
            return true;
        }

        //Search related object
        $this->_searchRelatedObject($obj, $map, IDbPersister_Map::OPERATION_CHECK_BEFORE_DELETE, $used_keys, $used_values);
        if($obj->keysFilled()) {
            
            //Object founds
            if($condition === IDbPersister_Map::CONDITION_RESTRICT) {
                //Key must be present
                throw new Exception("RELATIONSHIP ERROR: 
                                    Method: ".__METHOD__."
                                    Object: ".get_class($obj)." 
                                    Keys: ".var_export($used_keys, true)."
                                    Dump: ".var_export($this, true));
            }
            
        }
        else {
            //Object not founds
            return true;
        }
    }

    
    /**
     * This method as no sense here but can be implemented by users
     * who need it to do customs operations after insert objects
     * 
     * @param DbMySql_Persister $obj
     * @param array $map
     * @throws Exception
     */
    function fireOnAfterInsertRelatedObject(DbMySql_Persister $obj, $map)
    {
        throw new Exception("UNIMPLEMENTED METHOD: 
                                    Method: ".__METHOD__."
                                    Object: ".get_class($obj)." 
                                    Map:    ".var_export($map, true)
                                    );
    }
    
    /**
     * Joined fields with that must do one or more
     * operation after update
     * 
     * Conditions supported:
     *  CONDITION_IGNORE    -> ignore if the related object doesn't exists
     *  CONDITION_CASCADE   -> update related classes with the related keys
     * 
     * @param array $rel_fields
     */
    function fireOnAfterUpdateRelatedObject(DbMySql_Persister $obj, $map)
    {        
        $condition = @$map[IDbPersister_Map::OPERATION_EXECUTE_AFTER_UPDATE];
        if(empty($condition)) {
            return true;
        }
        
        //Search related object
        $this->_searchRelatedObject($obj, $map, IDbPersister_Map::OPERATION_EXECUTE_AFTER_UPDATE, $used_keys, $used_values);
        if($obj->keysFilled()) {
            
            //Object founds, run operations after update
            
            //Verify condition to do
            if($condition === IDbPersister_Map::CONDITION_IGNORE) {
                //No problems here
                return true;
            }
            else if($condition === IDbPersister_Map::CONDITION_CASCADE) {
                
                //Retreive original fields
                $origFields = $obj->getFields();
                
                //Remove fields that are not presents into relationship
                if(!empty($origFields)) {
                    foreach($origFields as $field=>$not_used_value):
                        if(!array_key_exists($field, $used_keys)) {
                            $obj->unsetField($field);
                        }
                    endforeach;
                }
                
                //Setting and removing new fields and keys
                $newWhereKeys = array();
                foreach($used_keys as $rel_field=>$old_value):
                    $thisField = key($old_value);
                    $obj->$rel_field = $this->$thisField;                
                    $newWhereKeys[$rel_field] = $old_value;
                endforeach;
                
                //After removing unuset fields reset the primary key to modify the where clause
                $obj->setOrigKeyValues($newWhereKeys);

                //echo $obj->getUpdateSql();
                //Execution of update
                $obj->update();
                
            }
            
        }
        else {
            //No objects founds, no operations after update
            return true;
        }
        
        
    }
    
    /**
     * Joined fields with that must do one or more
     * operation after delete
     * 
     * Conditions supported:
     *  CONDITION_IGNORE    -> ignore if the related object doesn't exists
     *  CONDITION_CASCADE   -> delete related classes with the related keys
     * 
     * @param array $rel_fields
     */
    function fireOnAfterDeleteRelatedObject(DbMySql_Persister $obj, $map)
    {

        $condition = @$map[IDbPersister_Map::OPERATION_EXECUTE_AFTER_DELETE];
        if($condition === IDbPersister_Map::CONDITION_IGNORE) {
            //No problems here
            return true;
        }

        //Search related object
        $this->_searchRelatedObject($obj, $map, IDbPersister_Map::OPERATION_EXECUTE_AFTER_DELETE, $used_keys, $used_values);
        
        //Verify condition to do            
        if($condition === IDbPersister_Map::CONDITION_CASCADE) {

            $rs = $obj->find($used_keys, $used_values, 0, 0, true);        
            if($rs && $rs->num_rows > 0) {            
                //Delete all objects founds
                $className = get_class($obj);
                while($el = $rs->fetch_object($className))
                {
                    $el->storeOriginalValues();
                    $el->delete();
                }             
            }
            else {
                //No objects founds, no operations after update
                return true;
            }

            return true;
        }
        else if($condition === IDbPersister_Map::CONDITION_SET_NULL) {

            if($obj->keysFilled()) {
                //Retreive original fields
                $origFields = $obj->getFields();

                //Remove fields that are not presents into relationship
                if(!empty($origFields)) {
                    foreach($origFields as $field=>$not_used_value):
                        if(!array_key_exists($field, $used_keys)) {
                            $obj->unsetField($field);
                        }
                    endforeach;
                }

                //Setting and removing new fields and keys
                $newWhereKeys = array();
                foreach($used_keys as $rel_field=>$old_value):
                    $obj->$rel_field = NULL;                
                    $newWhereKeys[$rel_field] = $old_value;
                endforeach;

                //After removing unuset fields reset the primary key to modify the where clause
                $obj->setOrigKeyValues($newWhereKeys);

                //Execution of update
                $obj->update();

            }

            return true;
        }
            
            
    }

    

}



?>